#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3
import os 
import sys
from datetime import datetime, date 
from operator import itemgetter 


start_date_field = 0
duration_field = 1
amount_field = 2
rate_field = 3
bank_field = 4


class TermDepositManager:
    def __init__(self, filename):
        self.dbname = filename
        conn = sqlite3.connect(self.dbname)
        c = conn.cursor()
        c.execute('''select * from Fix''')
        self.items = c.fetchall()
        conn.close()

    def add_due_field(self):
        conn = sqlite3.connect(self.dbname)
        c = conn.cursor()
        c.execute('''DROP TABLE Fix2''')
        c.execute('''CREATE TABLE Fix2 (start_date text, duration integer, end_date text, amount real, rate real, end_amount real, bank text)''')
        for record in self.items:
            start_date = datetime.strptime(record[start_date_field], '%Y-%m-%d')
            start_date = start_date.date()
            duration_months = record[duration_field]
            principal = record[amount_field]
            rate = record[rate_field]
            bank = record[bank_field]
            end_date = TermDepositManager.__get_due_date__(start_date, duration_months)
            due_value = TermDepositManager.__get_due_value__(principal, duration_months, rate/100.0) 
            detail_record = (start_date, duration_months, end_date, principal, rate, due_value, bank)
            statement = "INSERT INTO Fix2 VALUES ('%s', %d, '%s', %f, %f, %.2f, '%s')" % detail_record; 
            c.execute(statement)

        conn.commit()
        conn.close()

    def report_basics(self):
        sum_capital = self.query_sum()
        due_count = self.query_due()
        TermDepositManager.__print_line__()
        print len(self.items), 'records, RMB',  sum_capital , ' in total, ', due_count, 'records due'
        TermDepositManager.__print_line__()

    def report_details(self):
        detail_list = []
        for record in self.items:
            start_date = datetime.strptime(record[start_date_field], '%Y-%m-%d')
            start_date = start_date.date()
            duration_months = record[duration_field]
            principal = record[amount_field]
            rate = record[rate_field]
            bank = record[bank_field]
            end_date = TermDepositManager.__get_due_date__(start_date, duration_months)
            due_value = TermDepositManager.__get_due_value__(principal, duration_months, rate/100.0) 
            detail_record = (start_date, end_date, duration_months, principal, due_value, bank)
            detail_list.append(detail_record)

        TermDepositManager.__print_line__()
        detail_list.sort(key=itemgetter(1))
        for record in detail_list:
            print u"%s-->%s (%d month) ￥%.2f-->￥%.2f, %s" % record

        TermDepositManager.__print_line__()

    
    def report(self):
        self.report_basics()
        self.report_details()


    def query_sum(self):
        all = 0
        for record in self.items:
            all += record[amount_field]
       
        return all 

    @staticmethod
    def __get_due_date__(start_date, month_delta):
        end_month = (start_date.month + month_delta) % 12
        end_year = start_date.year + (start_date.month + month_delta) / 12
        end_date = date(end_year, end_month, start_date.day)
        return end_date

    @staticmethod
    def __get_due_value__(principal, month_delta, year_rate):
        return  principal * (year_rate/12.0 * month_delta + 1)

    @staticmethod
    def __print_line__():
        print '****************************************'

    def query_due(self):
        due_count = 0
        for record in self.items:
            start_date = datetime.strptime(record[start_date_field], '%Y-%m-%d') 
            duration_months = record[duration_field]
            end_date = TermDepositManager.__get_due_date__(start_date, duration_months)
            if end_date < date.today(): due_count += 1

        return due_count

dbname = sys.argv[1]
manager = TermDepositManager(dbname)
#manager.report()
manager.add_due_field()





